package com.project.dao.dao.impl;

import com.project.bean.User;
import com.project.dao.IUserDao;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户实体接口的实现类
 */

public class UserImpl extends BaseDao implements IUserDao {
    @Override
    public void addUser(User u) {
        this.setConnection();
        try {
            ps = con.prepareStatement("insert into t_user(u_name,u_pwd,u_birthday,u_rank)values(?,?,?,?)");
            ps.setObject(1,u.getName());
            ps.setObject(2,u.getPwd());
            ps.setObject(3,u.getBirthday());
            ps.setObject(4,u.getRank());

            ps.executeUpdate();  //更新数据库

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public void del(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("delete from t_user where pk_userId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void update(int id, String pwd, String rank) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_user set u_pwd=?,u_rank=? where pk_userId=?");
            ps.setObject(1,pwd);
            ps.setObject(2,rank);
            ps.setObject(3,id);

            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            this.closeConnection();
        }
    }

    @Override
    public List<User> findAll() {
        List<User> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_user");
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(this.getUser());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }
        return list;
    }

    @Override
    public User findById(int id) {
        User u = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where pk_userId=?");
            ps.setObject(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                u = new User();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPwd(rs.getString("u_pwd"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setRank(rs.getString("u_rank"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return u;
    }

    @Override
    public List<User> findByName(String name) {
        List<User> list = new ArrayList<>();
        this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_user where u_name like ?");
            ps.setObject(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(this.getUser());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }
        return list;
    }

    /**
     * 从结果集中得到数据，封装成用户对象
     * @return 用户对象
     */
    private User getUser() throws SQLException {
        User u = new User();
        u.setId(rs.getInt("pk_userId"));
        u.setName(rs.getString("u_name"));
        u.setPwd(rs.getString("u_pwd"));
        u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
        u.setRank(rs.getString("u_rank"));
        return u;
    }

    @Override
    public List<User> findByBirthday(LocalDate startDate, LocalDate endDate) {
        List<User> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where u_birthday>=? and u_birthday<=?");
            ps.setObject(1,startDate.toString());
            ps.setObject(2,endDate.toString());

            rs = ps.executeQuery();
            while (rs.next()){
                User u = new User();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPwd(rs.getString("u_pwd"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setRank(rs.getString("u_rank"));
                list.add(u);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<User> findByRank(String rank) {
        List<User> list = new ArrayList<>();
       this.setConnection();
        try {
            ps = con.prepareStatement("select * from t_user where u_rank=?");
            ps.setObject(1,rank);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(this.getUser());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
           this.closeConnection();
        }
        return list;
    }

    @Override
    public User login(String userName, String pwd) {



        return null;
    }


    public static void main(String[] args) {
        IUserDao userDao = new UserImpl();
//        userDao.addUser(new User("888","321",LocalDate.parse("1988-09-18"),"普通用户"));
//        userDao.del(1);
//        userDao.update(2,"222","管理员");
//        System.out.println(userDao.findAll());
//        System.out.println(userDao.findById(2));
//        System.out.println(userDao.findByName("2"));
//        System.out.println(userDao.findByBirthday(LocalDate.parse("1990-01-01"),LocalDate.parse("1999-12-30")));
        System.out.println(userDao.findByRank("普通用户"));
    }
}